package com.awspaas.user.apps.cpq.util;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import com.actionsoft.bpms.bo.engine.BO;
import com.actionsoft.bpms.commons.database.RowMap;
import com.actionsoft.bpms.util.DBSql;
import com.alibaba.fastjson.JSONObject;

public class RefreshModulDataUtils {
	public static  Map<String, Map<String, Object>> getModule( Map<String, JSONObject>  zpjNewMap ) {
		try {
			//2、获取所有状态为启用的模块信息BO_EU_CPQ_MODULE_MSG
			 String allModelsql="select   a.MKBM,a.MKDJ,"
			 		+ "a.CLF,a.ZZFBL,"
			 		+ "a.RGF,a.WXWG ,a.CGF,a.ZL,a.TJ,a.SITRUE,b.ID,b.WLBM,b.LX ," + 
			 		"b.SL,b.WLJSGS,b.DJ as JG,"
			 		+ "b.CLF as WCLF,"
			 		+ "b.RGF as WRGF,b.WXWG as WX,b.CGF as WCGF,b.ZL as WZL,b.TJ as WTJ,"
			 		+ "b.ZJ,"
			 		+ "b.ZCLF,"
			 		+ "b.ZGSF,b.ZWX,b.ZCGF,b.ZZL,b.ZTJ " + 
			 		"from  BO_EU_CPQ_MODULE_MSG a,BO_EU_CPQ_MODULE_MSG_GRID b  "
			 		+ "where  a.BINDID=b.BINDID and  a.STATUS='启用'";
			 List<RowMap> modelmaps=DBSql.getMaps(allModelsql);
			 //存放需要更新的模板信息
			 Map<String , List<RowMap>> modelMap=new  HashMap<>();
			 if(modelmaps.size()>0) {
				 for (RowMap rowMap : modelmaps) {
					 String bh=rowMap.getString("MKBM");
						if(modelMap.containsKey(bh)) {
						  List<RowMap> lists= modelMap.get(bh);
						  lists.add(rowMap);
						  modelMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							modelMap.put(bh, lists);
						}
				}
			 }
			 //获取参数信息
			 String csxx="select  b.MKBM,a.PARMENTIER_NO,a.PARMENTIER_VALUE from BO_EU_CPQ_MODULE_MSG_PRICE_PARAMETERS  a , BO_EU_CPQ_MODULE_MSG b where  a.BINDID=b.BINDID and  b.STATUS='启用'";
			 List<RowMap> cs=DBSql.getMaps(csxx); 
			 //存放模块关联的参数信息
			 Map<String , List<RowMap>> parmMap=new  HashMap<>();
			 if(cs.size()>0) {
				 for (RowMap rowMap : cs) {
					 String bh=rowMap.getString("MKBM");
						if(parmMap.containsKey(bh)) {
						  List<RowMap> lists= parmMap.get(bh);
						  lists.add(rowMap);
						  parmMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							parmMap.put(bh, lists);
						}
				}
			 }
			 String  sql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		+ "CLF=?,"
			 		+ "RGF=?,WXWG=?,CGF=?,ZL=?,TJ=?,WLMC=?,LX=?,GG=?,DH=?,DW=? "
				 		+ "where WLBM=? and  ID=?";
			 String  jssql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		+ "CLF=?,"
			 		+ "RGF=?,WXWG=?,CGF=?,ZL=?,TJ=?"
			 		+ ",ZJ=?,"
				 		+ "ZCLF=?,"
				 		+ "ZGSF=?,ZWX=?,ZCGF=?,ZZL=?,ZTJ=?,WLMC=?,LX=?,GG=?,DH=?,DW=? where WLBM=? and  ID=?";
			 String modulsql="update BO_EU_CPQ_MODULE_MSG set MKDJ=?,"
			 		+ "CLF=?,"
			 		+ "RGF=?,WXWG=?,CGF=?,ZL=?,TJ=?,ZZF=?,DJZZF=? "
				 		+ " where MKBM=? ";
			 //用于存放更新的模块编码
			Map<String, Map<String, Object>>modulLists=new HashMap<>();
			List<String> sqlList=new ArrayList<>();
			List<String> sqlLists=new ArrayList<>();
			 if(modelMap.size()>0) {
				
				 for (String key : modelMap.keySet()) {
					 Workbook workbook = new XSSFWorkbook();
					 try {
							List<RowMap> materials=modelMap.get(key);
							 RowMap data=materials.get(0);
							 String dj=data.getString("MKDJ");//单价	
							 BigDecimal djnum=new BigDecimal(dj);					 
							 String BH=data.getString("MKBM");//物料编号
							 String SITRUE=data.getString("SITRUE");//是否动态计算模块
							 String clf=data.getString("CLF").isEmpty()?"0":data.getString("CLF");//材料费					 
							 BigDecimal clfnum=new BigDecimal(clf);						
							 String rgf=data.getString("RGF").isEmpty()?"0":data.getString("RGF");//人工费
							 BigDecimal rgfnum=new BigDecimal(rgf);	
							 String wxf=data.getString("WXWG").isEmpty()?"0":data.getString("WXWG");//外协费
							 BigDecimal wxfnum=new BigDecimal(wxf);	
							 String cgf=data.getString("CGF").isEmpty()?"0":data.getString("CGF");//采购费
							 BigDecimal cgfnum=new BigDecimal(cgf);	
							 String zl=data.getString("ZL").isEmpty()?"0":data.getString("ZL");//重量
							 BigDecimal zlnum=new BigDecimal(zl);	
							 String tj=data.getString("TJ").isEmpty()?"0":data.getString("TJ");//体积
							 BigDecimal tjnum=new BigDecimal(tj);	
							 //存放动态计算的模块物料信息
							 List<RowMap> lists=new  ArrayList<>();
							 String isTrue="0"; 
							 //将参数进行遍历放置：
							 List<RowMap> parms=new ArrayList<>();
							 if(parmMap.containsKey(key)) {
								 parms=parmMap.get(key);
							 }							  
							 Map<String, String> noMap = new HashMap<>();
							 Map<String, String> pMap = new HashMap<String, String>();
							 Sheet sheet = workbook.createSheet("Sheet1");
							 if(parms.size()>0) {
								
									for (int i = 0; i < parms.size(); i++) {
										int num = i + 1;
										Cell cellB1 = sheet.createRow(i).createCell(1);
										//JSONObject jsonObject = (JSONObject) list.get(i);
										RowMap jsonObject =  parms.get(i);
										String bh =  jsonObject.getString("PARMENTIER_NO");
										String value=	jsonObject.getString("PARMENTIER_VALUE");
										if(NumberUtils.isCreatable(value)) {
											double  numValue=Double.parseDouble(value);		
											 cellB1.setCellValue(numValue);
											 System.out.println("cellB1:"+cellB1.getNumericCellValue());
										}else {
											cellB1.setCellValue(value);
											System.out.println("cellB1:"+cellB1.getStringCellValue());											
										}
										noMap.put(bh, "B$" + num);
										pMap.put(bh, value);
									}
							 }
							for (int i = 0; i < materials.size(); i++) {
								 //获取模块子项的物料明细
								RowMap rowMap = materials.get(i);
								String BM=rowMap.getString("WLBM");//编码		
								String id=rowMap.getString("ID");//id	
								String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//数量
								BigDecimal sl=new BigDecimal(SL);
								String DJ=rowMap.getString("JG");//单价						
								String ZJ=rowMap.getString("ZJ");//总价						
								String ZCLF=rowMap.getString("ZCLF").isEmpty()?"0":rowMap.getString("ZCLF");//总材料费						
								String ZGSF=rowMap.getString("ZGSF").isEmpty()?"0":rowMap.getString("ZGSF");//总人工费
								String ZWX=rowMap.getString("ZWX").isEmpty()?"0":rowMap.getString("ZWX");//总外协
								String ZCGF=rowMap.getString("ZCGF").isEmpty()?"0":rowMap.getString("ZCGF");//总采购费
								String ZZL = rowMap.getString("ZZL").isEmpty()?"0":rowMap.getString("ZZL");// 总重量
								String ZTJ = rowMap.getString("ZTJ").isEmpty()?"0":rowMap.getString("ZTJ");// 总体积
								BigDecimal zjNew=new BigDecimal(ZJ);						
								BigDecimal zclfNew=new BigDecimal(ZCLF);						
								BigDecimal zgsfNew=new BigDecimal(ZGSF);
								BigDecimal zwxNew=new BigDecimal(ZWX);
								BigDecimal zcgfNew=new BigDecimal(ZCGF);
								BigDecimal zzlNew = new BigDecimal(ZZL);
								BigDecimal ztjNew = new BigDecimal(ZTJ);
								if(zpjNewMap.containsKey(BM)) {
									JSONObject json=zpjNewMap.get(BM);	
									
									String newdj=json.getString("DJ");// 													
									String newclf=json.getString("CLF");							
									String newrgf=json.getString("RGF");
									String newwx=json.getString("WXWG");
									String newcgf=json.getString("CGF");
									String newzl=json.getString("ZL");
									String newtj=json.getString("TJ");
									BigDecimal djNew=new BigDecimal(newdj);							
									BigDecimal clfNew=new BigDecimal(newclf);							
									BigDecimal rgfNew=new BigDecimal(newrgf);
									BigDecimal wxNew=new BigDecimal(newwx);
									BigDecimal cgfNew=new BigDecimal(newcgf);
									BigDecimal zlNew=new BigDecimal(newzl);
									BigDecimal tjNew=new BigDecimal(newtj);
									 isTrue="1";
									 //判断是否为动态计算模块
									 if(true) {
										 //物料数量计算公司
										 String equation = rowMap.getString("WLJSGS");
										 //存在计算公式时，只需要更新物料的单价，材料费、人工费、采购费、外协费
										 Document document = Jsoup.parse(equation);
										  String newQuation=document.text();
										  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
											  System.out.println("参数计算公式："+newQuation);
												// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
											  if(pMap.size()>0) {
												  for (String key1 : pMap.keySet()) {
														int num = newQuation.indexOf(key1);
														if (num > -1) {
															newQuation = newQuation.replace(key1, noMap.get(key1));
														}
													}
													System.out.println("转换后的参数计算公式："+newQuation);
													// 计算出物料数量和当前模块的物料成本价格
													FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
													Cell cellB1 = sheet.createRow(cs.size()+i).createCell(2);
													cellB1.setCellFormula(newQuation);
													formulaEvaluator.evaluateFormulaCell(cellB1);
													// 获取公式计算结果
													CellValue cellValue = formulaEvaluator.evaluate(cellB1);
													BigDecimal number=new BigDecimal(cellValue.getNumberValue());	
													sl=number;  
											  }												
												
										  }
											djnum=djnum.subtract(zjNew).add(djNew.multiply(sl));
												//新的物料总价为新的单价x数量									
												zjNew=djNew.multiply(sl);										
												//新的物料总材料费为新的材料费x数量											
												zclfNew=sl.multiply(clfNew);										
												//新的物料总人工费为新的人工费x数量											
												zgsfNew=sl.multiply(rgfNew);
												//新的物料总外协费为新的外协费x数量										
												zwxNew=sl.multiply(wxNew);
												//新的物料总采购费为新的采购费x数量										
												zcgfNew=sl.multiply(cgfNew);
												//新的物料总重量为新的重量x数量										
												zzlNew=sl.multiply(zlNew);
												//新的物料总体积为新的体积x数量										
												ztjNew=sl.multiply(tjNew);
												 rowMap.put("JG", newdj);										 
												 rowMap.put("WCLF", newclf);										 
												 rowMap.put("WRGF", newrgf);
												 rowMap.put("WX", newwx);
												 rowMap.put("WCGF", newcgf);
												 rowMap.put("WZL", newzl);
												 rowMap.put("WTJ", newtj);
												 //ZCLF=?,ZGSF=?,ZWX=?,ZCGF=?
												 rowMap.put("ZCLF", zclfNew);										 							
												 rowMap.put("ZGSF", zgsfNew);
												 rowMap.put("ZWX", zwxNew);
												 rowMap.put("ZCGF", zcgfNew);
												 rowMap.put("ZJ", zjNew);										 
												 rowMap.put("ZZL", zzlNew);
												 rowMap.put("ZTJ", ztjNew);
												 rowMap.put("SL", sl);
												//DBSql.update(jssql,new Object[] {newdj,newclf,newrgf,newwx,newcgf,newzl,newtj,zjNew,zclfNew,zgsfNew,zwxNew,zcgfNew,zzlNew,ztjNew,json.getString("NAME"),json.getString("TYPE"),json.getString("GG"),json.getString("TH"),json.getString("DW"),BM,id});
//												 String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set DJ= '"+newdj+"',CLF= '"+newclf+"' ,RGF='"+newrgf+"',WXWG='"+newwx+"',"
//												 		+ "CGF='"+newcgf+"',ZJ='"+zjNew+"',ZCLF='"+zclfNew+"',ZGSF='"+zgsfNew+"',ZWX='"+zwxNew+"',ZCGF='"+zcgfNew+"'"
//												 		+ ",WLMC='"+json.getString("NAME")+"',LX='"+json.getString("TYPE")+"',GG='"+json.getString("GG")+"',DH='"+json.getString("TH")+"',DW='"+json.getString("DW")+"' where WLBM='"+BM+"' and  ID='"+id+"' ; ";
//													//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
//													sqlList.add(upSql);
													 //不是动态计算模块时，需要计算每个物料的价格和模块的价格								
													//新的装配件总价=原装配件单价-子件总价+新子件单价x子件数量
													
														zjNew=djNew.multiply(sl);					
													//判断材料费是否相等,不相等，则计算当前装配件的材料费，并计算当前装配件子项的物料的材料费。								
													 String CLF=rowMap.getString("WCLF");//材料费								
													    BigDecimal clfOld=new BigDecimal(CLF);
													   
														
															BigDecimal zclfOld=new BigDecimal(ZCLF);
															zclfNew=sl.multiply(clfNew);
															clfnum=clfnum.subtract(zclfOld).add(sl.multiply(clfNew));
																							
													//判断人工费是否相等,不相等，则计算当前装配件的人工费，并计算当前装配件子项的物料的人工费。	
													 
													 String RGF=rowMap.getString("WRGF");//人工费
														BigDecimal rgfOld=new BigDecimal(RGF);
														
															BigDecimal zrgfOld=new BigDecimal(ZGSF);
															zgsfNew=sl.multiply(rgfNew);
															rgfnum=rgfnum.subtract(zrgfOld).add(sl.multiply(rgfNew));
														
													//判断外协费是否相等,不相等，则计算当前装配件的外协费，并计算当前装配件子项的物料的外协费。	
													
													 String WXWG=rowMap.getString("WX");//外协费
														BigDecimal wxOld=new BigDecimal(WXWG);
														
															BigDecimal zwxOld=new BigDecimal(ZWX);
															zwxNew=sl.multiply(wxNew);
															wxfnum=wxfnum.subtract(zwxOld).add(sl.multiply(wxNew));
														
													//判断采购费是否相等,不相等，则计算当前装配件的采购费，并计算当前装配件子项的物料的采购费。	
													
													 String CGF=rowMap.getString("WCGF").isEmpty()?"0":rowMap.getString("WCGF");//采购费
														BigDecimal cgfOld=new BigDecimal(CGF);
														
															BigDecimal zcgfOld=new BigDecimal(ZCGF);
															zcgfNew=sl.multiply(cgfNew);
															cgfnum=cgfnum.subtract(zcgfOld).add(sl.multiply(cgfNew));
														
													//判断采购费是否相等,不相等，则计算当前装配件的采购费，并计算当前装配件子项的物料的采购费。	
														
//														 String ZL=rowMap.getString("WZL").isEmpty()?"0":rowMap.getString("WZL");//重量
//															BigDecimal zlOld=new BigDecimal(ZZL);
//															
//																BigDecimal zzlOld=new BigDecimal(ZZL);
//																zzlNew=sl.multiply(zlNew);
//																zlnum=zlnum.subtract(zzlOld).add(sl.multiply(zlNew));
															
															//判断采购费是否相等,不相等，则计算当前装配件的采购费，并计算当前装配件子项的物料的采购费。	
															
//															 String TJ=rowMap.getString("WTJ").isEmpty()?"0":rowMap.getString("WTJ");//体积
//																BigDecimal tjOld=new BigDecimal(TJ);
//																
//																	BigDecimal ztjOld=new BigDecimal(ZTJ);
//																	ztjNew=sl.multiply(tjNew);
//																	tjnum=tjnum.subtract(ztjOld).add(sl.multiply(tjNew));
																
														//更新当前模块物料子项的数据。
														//DBSql.update(jssql,new Object[] {newdj,newclf,newrgf,newwx,newcgf,newzl,newtj,zjNew,zclfNew,zgsfNew,zwxNew,zcgfNew,zzlNew,ztjNew,json.getString("NAME"),json.getString("TYPE"),json.getString("GG"),json.getString("TH"),json.getString("DW"),BM,id});	
														 String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set SL='"+sl+"', DJ= '"+newdj+"',CLF= '"+newclf+"' ,RGF='"+newrgf+"',WXWG='"+newwx+"',"
															 		+ "CGF='"+newcgf+"',ZJ='"+zjNew+"',ZCLF='"+zclfNew+"',ZGSF='"+zgsfNew+"',ZWX='"+zwxNew+"',ZCGF='"+zcgfNew+"'"
															 		+ ",WLMC='"+json.getString("NAME")+"',LX='"+json.getString("TYPE")+"',GG='"+json.getString("GG")+"',DH='"+json.getString("TH")+"',DW='"+json.getString("DW")+"' where WLBM='"+BM+"' and  ID='"+id+"' ; ";
																//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
																sqlList.add(upSql);
										 		
										
										 
									 }
								}
								//将数据更新到lists集合中
								 lists.add(rowMap);
								 
								
							}
							
							//更新装配件数据。
							 if(isTrue.equals("1")) {			                    
			                            Map<String, Object> map=new HashMap<>();	    						    					    					
				    					JSONObject json=new JSONObject();
				    					json.put("SITRUE", SITRUE);
				    					json.put("MKDJ", djnum);		    					
										json.put("CLF", clfnum);					
				    					json.put("RGF", rgfnum);
				    					json.put("WXWG", wxfnum);
				    					json.put("CGF", cgfnum);
				    					json.put("ZL", zlnum);
				    					json.put("TJ", tjnum);
				    					json.put("BM", key);
				    					map.put("SITRUE", SITRUE);	
				    					map.put("datas", lists);
				    					modulLists.put(BH, map);
				    					String bl=data.getString("ZZFBL").isEmpty()?"0":data.getString("ZZFBL");		    					
				    					BigDecimal blnum = new BigDecimal(bl);
				    					BigDecimal num = new BigDecimal("100");
				    					BigDecimal num1 = new BigDecimal("1");
				    					BigDecimal zzf=	clfnum.add(rgfnum).divide(num1.subtract(blnum.divide(num)), 4).multiply(blnum.divide(num));						    							    					
				    					// DBSql.update(modulsql, new Object[] {djnum,clfnum rgfnum,wxfnum,cgfnum,zlnum,tjnum,zzf,zzf.add(djnum),key});	
				   						 String upSql= "update BO_EU_CPQ_MODULE_MSG set MKDJ= '"+djnum+"',CLF= '"+clfnum+"' ,RGF='"+rgfnum+"',WXWG='"+wxfnum+"',CGF='"+cgfnum+"',ZZF='"+zzf+"',DJZZF='"+zzf.add(djnum)+"' where MKBM='"+key+"' ; ";
											//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											sqlLists.add(upSql);
				    				
							 }	
						
					} catch (Exception e) {
						throw e;
					}finally {
			  			 try {
							workbook.close();
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
				
					}
					 
					 
				 }
				 if(sqlList.size()>0) {
					 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
					 DBSql.batch(array3);
				}
				if(sqlLists.size()>0) {
					String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);					
					DBSql.batch(array2);  
				}	

			 }
			return modulLists;
			
		} catch (Exception e) {
			throw e;
		}
		
		
	}
	public static  Map<String, Map<String, Object>> getPorpertiesModule( Map<String, JSONObject>  comtMapNew ) {
		try {
			//2、获取所有状态为启用的模块信息BO_EU_CPQ_MODULE_MSG
			 String allModelsql="select   a.MKBM,a.MKDJ,"
			 		+ "a.CLF,a.ZZFBL,"
			 		+ "a.RGF,a.WXWG ,a.CGF,a.ZL,a.TJ,a.SITRUE,b.ID,b.WLBM,b.LX ," + 
				 		"b.SL,b.WLJSGS,b.DJ as JG,"
				 		+ "b.CLF as WCLF,"
				 		+ "b.RGF as WRGF,b.WXWG as WX,b.CGF as WCGF,b.ZL as WZL,b.TJ as WTJ,"
				 		+ "b.ZJ,"
				 		+ "b.ZCLF,"
				 		+ "b.ZGSF,b.ZWX,b.ZCGF,b.ZZL,b.ZTJ " + 
				 		"from  BO_EU_CPQ_MODULE_MSG a,BO_EU_CPQ_MODULE_MSG_GRID b  "
				 		+ "where  a.BINDID=b.BINDID and  a.STATUS='启用'";
			 
			 List<RowMap> modelmaps=DBSql.getMaps(allModelsql);
			 //存放需要更新的模板信息
			 Map<String , List<RowMap>> modelMap=new  HashMap<>();
			 if(modelmaps.size()>0) {
				 for (RowMap rowMap : modelmaps) {
					 String bh=rowMap.getString("MKBM");
						if(modelMap.containsKey(bh)) {
						  List<RowMap> lists= modelMap.get(bh);
						  lists.add(rowMap);
						  modelMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							modelMap.put(bh, lists);
						}
				}
			 }
			//获取参数信息
			 String csxx="select  b.MKBM,a.PARMENTIER_NO,a.PARMENTIER_VALUE from BO_EU_CPQ_MODULE_MSG_PRICE_PARAMETERS  a , BO_EU_CPQ_MODULE_MSG b where  a.BINDID=b.BINDID and  b.STATUS='启用'";
			 List<RowMap> cs=DBSql.getMaps(csxx); 
			 //存放模块关联的参数信息
			 Map<String , List<RowMap>> parmMap=new  HashMap<>();
			 if(cs.size()>0) {
				 for (RowMap rowMap : cs) {
					 String bh=rowMap.getString("MKBM");
						if(parmMap.containsKey(bh)) {
						  List<RowMap> lists= parmMap.get(bh);
						  lists.add(rowMap);
						  parmMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							parmMap.put(bh, lists);
						}
				}
			 }
			 String  sql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		    + "CLF=?  "
				 		+ "where WLBM=? and  ID=?";
			 String  jssql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		      + "CLF=?,"
			 		      + "ZJ=?,"
				 		  + "ZCLF=? "
				 		  + "where WLBM=? and  ID=?";
			 String modulsql="update BO_EU_CPQ_MODULE_MSG set MKDJ=?,"
			 		        + "CLF=?,ZZF=?,DJZZF=? "
				 		    + " where MKBM=? ";
			 //用于存放更新的模块编码
				Map<String, Map<String, Object>>modulLists=new HashMap<>();
				  List<String> sqlList=new ArrayList<>();
				  List<String> sqlLists=new ArrayList<>();	
				 if(modelMap.size()>0) {
					 for (String key : modelMap.keySet()) {
						 Workbook workbook = new XSSFWorkbook();
						 try {							
							 List<RowMap> materials=modelMap.get(key);
							 RowMap data=materials.get(0);
							 String dj=data.getString("MKDJ").isEmpty()?"0":data.getString("MKDJ");//单价												 
							 BigDecimal djnum=new BigDecimal(dj);						 						 
							 String BH=data.getString("MKBM");//物料编号
							 String SITRUE=data.getString("SITRUE");//是否动态计算模块						 
							 String clf=data.getString("CLF");//材料费												 
							 BigDecimal clfnum=new BigDecimal(clf);							
							 //存放动态计算的模块物料信息
							 List<RowMap> lists=new  ArrayList<>(); 
							 String isTrue="0"; 
							 //将参数进行遍历放置：
							 List<RowMap> parms=new ArrayList<>();
							 if(parmMap.containsKey(key)) {
								 parms=parmMap.get(key);
							 }
							 Map<String, String> noMap = new HashMap<>();
							 Map<String, String> pMap = new HashMap<String, String>();
							 Sheet sheet = workbook.createSheet("Sheet1");
							 if(parms.size()>0) {
									
									for (int i = 0; i < parms.size(); i++) {
										int num = i + 1;
										Cell cellB1 = sheet.createRow(i).createCell(1);
										//JSONObject jsonObject = (JSONObject) list.get(i);
										RowMap jsonObject =  parms.get(i);
										String bh =  jsonObject.getString("PARMENTIER_NO");
										String value=	jsonObject.getString("PARMENTIER_VALUE");
										if(NumberUtils.isCreatable(value)) {
											double  numValue=Double.parseDouble(value);		
											 cellB1.setCellValue(numValue);
											 System.out.println("cellB1:"+cellB1.getNumericCellValue());
										}else {
											cellB1.setCellValue(value);
											System.out.println("cellB1:"+cellB1.getStringCellValue());											
										}
										noMap.put(bh, "B$" + num);
										pMap.put(bh, value);
									}
							 }
							 for (int i = 0; i < materials.size(); i++) {
								 //获取模块子项的物料明细
								 RowMap rowMap = materials.get(i);
									String BM=rowMap.getString("WLBM");//编码		
									String id=rowMap.getString("ID");//id	
									String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//数量
									BigDecimal sl=new BigDecimal(SL);
									String DJ=rowMap.getString("JG");//单价								
									String ZJ=rowMap.getString("ZJ");//总价								
									String ZCLF=rowMap.getString("ZCLF").isEmpty()?"0":rowMap.getString("ZCLF");//总材料费								
									BigDecimal zjNew=new BigDecimal(ZJ);								
									BigDecimal zclfNew=new BigDecimal(ZCLF);								
									if(comtMapNew.containsKey(BM)) {
										JSONObject json=comtMapNew.get(BM);	
										//System.out.println("json"+json.toJSONString());
										String newdj=json.getString("DJ");//									
										String newclf=json.getString("CLF");									
										BigDecimal djNew=new BigDecimal(newdj);									
										BigDecimal clfNew=new BigDecimal(newclf);									
										isTrue="1";
										 //判断是否为动态计算模块
										 if(true) {
											 //物料数量计算公司
											 String equation = rowMap.getString("WLJSGS");
											 //存在计算公式时，只需要更新物料的单价，材料费、人工费、采购费、外协费
											 Document document = Jsoup.parse(equation);
											  String newQuation=document.text();
											  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
												  System.out.println("参数计算公式："+newQuation);
													// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
												  if(pMap.size()>0) {
													  for (String key1 : pMap.keySet()) {
															int num = newQuation.indexOf(key1);
															if (num > -1) {
																newQuation = newQuation.replace(key1, noMap.get(key1));
															}
														}
														System.out.println("转换后的参数计算公式："+newQuation);
														// 计算出物料数量和当前模块的物料成本价格
														FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
														Cell cellB1 = sheet.createRow(cs.size()+i).createCell(2);
														cellB1.setCellFormula(newQuation);
														formulaEvaluator.evaluateFormulaCell(cellB1);
														// 获取公式计算结果
														CellValue cellValue = formulaEvaluator.evaluate(cellB1);
														BigDecimal number=new BigDecimal(cellValue.getNumberValue());	
														sl=number;  
												  }
													
											  }
											 //存在计算公式时，只需要更新物料的单价，材料费、人工费、采购费、外协费
												    //新的物料总价为新的单价x数量									
													zjNew=djNew.multiply(sl);
													
													//新的物料总材料费为新的材料费x数量											
													zclfNew=sl.multiply(clfNew);
													
													 rowMap.put("JG", newdj);
													 
													 rowMap.put("WCLF", newclf);

													 rowMap.put("ZCLF", zclfNew);
													
													 rowMap.put("ZJ", zjNew);
													 rowMap.put("SL", sl);
													 /**
													  * DBSql.update(jssql,new Object[] {newdj,
												    		 newclf,
												    		 zjNew,
												    		 zclfNew,BM,id});
													  */
												     

													    //String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set DJ= '"+newdj+"',CLF= '"+newclf+"' ,ZJ='"+zjNew+"',ZCLF='"+zclfNew+"' where WLBM='"+BM+"' and ID='"+id+"'; ";
														//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
														//sqlList.add(upSql);
														 //不是动态计算模块时，需要计算每个物料的价格和模块的价格。										
													     //新的装配件总价=原装配件单价-子件总价+新子件单价x子件数量
														djnum=djnum.subtract(zjNew).add(djNew.multiply(sl));
														zjNew=djNew.multiply(sl);										
														//判断材料费是否相等,不相等，则计算当前装配件的材料费，并计算当前装配件子项的物料的材料费。											
														String CLF=rowMap.getString("WCLF");//材料费
													    BigDecimal clfOld=new BigDecimal(CLF);									   
												if (clfNew.compareTo(clfOld) != 0) {
													BigDecimal zclfOld = new BigDecimal(ZCLF);
													zclfNew = sl.multiply(clfNew);
													clfnum = clfnum.subtract(zclfOld).add(sl.multiply(clfNew));
												}
												
														//更新当前模块物料子项的数据。												
												/**
												 *  DBSql.update(jssql,new Object[] {newdj,
												 *  newclf,
											    		 zjNew,
											    		 zclfNew,BM,id});	
												 */
											    		 
												 String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set DJ= '"+newdj+"',CLF= '"+newclf+"' ,ZJ='"+zjNew+"',ZCLF='"+zclfNew+"' where WLBM='"+BM+"' and ID='"+id+"'; ";
													//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
													sqlList.add(upSql);
											  
										 }							 
									}
									//将数据更新到lists集合中
									 lists.add(rowMap);
							 }
							
							//更新装配件数据。
							
							 if(isTrue.equals("1")) {
			                      
				    				if(SITRUE.equals("false")) {
			                            Map<String, Object> map=new HashMap<>();	    						    					    					
				    					JSONObject json=new JSONObject();
				    					json.put("SITRUE", SITRUE);
				    					json.put("MKDJ", djnum);				    					
										json.put("CLF", clfnum);										
				    					json.put("BM", key);
				    					map.put("SITRUE", SITRUE);	
				    					map.put("datas", lists);
				    					modulLists.put(BH, map);
				    					String bl=data.getString("ZZFBL").isEmpty()?"0":data.getString("ZZFBL");
				    					String rgf=data.getString("RGF");
				    					BigDecimal blnum = new BigDecimal(bl);
				    					BigDecimal rgfnum = new BigDecimal(rgf);
				    					BigDecimal num = new BigDecimal("100");
				    					BigDecimal num1 = new BigDecimal("1");
				    					BigDecimal zzf=	clfnum.add(rgfnum).divide(num1.subtract(blnum.divide(num)), 4).multiply(blnum.divide(num));					 
								 		   String upSql= "update BO_EU_CPQ_MODULE_MSG set MKDJ= '"+djnum+"',CLF= '"+clfnum+"' ,ZZF='"+zzf+"',DJZZF='"+zzf.add(djnum)+"' where MKBM='"+key+"' ; ";
											//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											sqlLists.add(upSql);
				    				}
							 }	
						 
						} catch (Exception e) {
							throw e;
						}finally {
				  			 try {
									workbook.close();
								} catch (IOException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
						
							}
					 }
					 if(sqlList.size()>0) {
						 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
						 DBSql.batch(array3);
					}
					if(sqlLists.size()>0) {
						String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);					
						DBSql.batch(array2);  
					}	 
					 
				 }
			return modulLists;	
		} catch (Exception e) {
			throw e;
		}
		
		
		
		
	}
	/**
	 * 对人工单价进行更新后，模块中的模块单价和人工费进行更新
	 * @param comtMap
	 * @return
	 */
	public static  Map<String, Map<String, Object>> wageRate‌RefreshModule( Map<String, JSONObject>  comtMap ) {
		try {
			//2、获取所有状态为启用的模块信息BO_EU_CPQ_MODULE_MSG
			 String allModelsql="select   a.MKBM,a.MKDJ,"
			 		+ "a.CLF,a.ZZFBL,"
			 		+ "a.RGF,a.WXWG ,a.CGF,a.ZL,a.TJ,a.SITRUE,b.ID,b.WLBM,b.LX ,b.SL,"
				 		+ "b.DJ as JG," 
				 		+ "b.CLF as WCLF,"			 	
				 		+ "b.RGF as WRGF,b.WXWG as WX,b.CGF as WCGF,b.ZL as WZL,b.TJ as WTJ,"
				 		+ "b.ZJ,"
				 		+ "b.ZCLF,b.ZGSF,b.ZWX,b.ZCGF,b.ZZL,b.ZTJ " + 
				 		"from  BO_EU_CPQ_MODULE_MSG a,BO_EU_CPQ_MODULE_MSG_GRID b  "
				 		+ "where  a.BINDID=b.BINDID and  a.STATUS='启用'";
			 
			 List<RowMap> modelmaps=DBSql.getMaps(allModelsql);
			 //存放需要更新的模板信息
			 Map<String , List<RowMap>> modelMap=new  HashMap<>();
			 if(modelmaps.size()>0) {
				 for (RowMap rowMap : modelmaps) {
					 String bh=rowMap.getString("MKBM");
						if(modelMap.containsKey(bh)) {
						  List<RowMap> lists= modelMap.get(bh);
						  lists.add(rowMap);
						  modelMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							modelMap.put(bh, lists);
						}
				}
			 }
			 //获取参数信息
			 String csxx="select  b.MKBM,a.PARMENTIER_NO,a.PARMENTIER_VALUE from BO_EU_CPQ_MODULE_MSG_PRICE_PARAMETERS  a , BO_EU_CPQ_MODULE_MSG b where  a.BINDID=b.BINDID and  b.STATUS='启用'";
			 List<RowMap> cs=DBSql.getMaps(csxx); 
			 //存放模块关联的参数信息
			 Map<String , List<RowMap>> parmMap=new  HashMap<>();
			 if(cs.size()>0) {
				 for (RowMap rowMap : cs) {
					 String bh=rowMap.getString("MKBM");
						if(parmMap.containsKey(bh)) {
						  List<RowMap> lists= parmMap.get(bh);
						  lists.add(rowMap);
						  parmMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							parmMap.put(bh, lists);
						}
				}
			 }
			 String  sql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		+ "RGF=?  "
				 		+ "where WLBM=? and  ID=?";
			 String  jssql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		+ "RGF=?,ZJ=?,"
				 		+ "ZGSF=? where WLBM=? and  ID=?";
			 String modulsql="update BO_EU_CPQ_MODULE_MSG set MKDJ=?,"
			 		+ "RGF=?,ZZF=?,DJZZF=? "
				 		+ " where MKBM=? ";
			 //用于存放更新的模块编码
				Map<String, Map<String, Object>>modulLists=new HashMap<>();
				List<String> sqlList=new ArrayList<>();
				List<String> sqlLists=new ArrayList<>();

				 if(modelMap.size()>0) {
					 for (String key : modelMap.keySet()) {
						 Workbook workbook = new XSSFWorkbook();
						 try {
							
							 List<RowMap> materials=modelMap.get(key);
							 RowMap data=materials.get(0);
							 String dj=data.getString("MKDJ");//单价
							 
							 BigDecimal djnum=new BigDecimal(dj);
							
							 String BH=data.getString("MKBM");//物料编号
							 String SITRUE=data.getString("SITRUE");//是否动态计算模块
							 String clf=data.getString("RGF");//人工费
							 BigDecimal clfnum=new BigDecimal(clf);	
							 //存放动态计算的模块物料信息
							 List<RowMap> lists=new  ArrayList<>();
							 String isTrue="0";
							 List<RowMap> parms=new ArrayList<>();
							 if(parmMap.containsKey(key)) {
								 parms=parmMap.get(key);
							 }
							 Map<String, String> noMap = new HashMap<>();
							 Map<String, String> pMap = new HashMap<String, String>();
							 Sheet sheet = workbook.createSheet("Sheet1");
							 if(parms.size()>0) {
								
									for (int i = 0; i < parms.size(); i++) {
										int num = i + 1;
										Cell cellB1 = sheet.createRow(i).createCell(1);
										//JSONObject jsonObject = (JSONObject) list.get(i);
										RowMap jsonObject =  parms.get(i);
										String bh =  jsonObject.getString("PARMENTIER_NO");
										String value=	jsonObject.getString("PARMENTIER_VALUE");
										if(NumberUtils.isCreatable(value)) {
											double  numValue=Double.parseDouble(value);		
											 cellB1.setCellValue(numValue);
											 System.out.println("cellB1:"+cellB1.getNumericCellValue());
										}else {
											cellB1.setCellValue(value);
											System.out.println("cellB1:"+cellB1.getStringCellValue());											
										}
										noMap.put(bh, "B$" + num);
										pMap.put(bh, value);
									}
							 }
							 for (int i = 0; i < materials.size(); i++) {
								//获取模块子项的物料明细
								 RowMap rowMap = materials.get(i);
								String BM=rowMap.getString("WLBM");//编码		
								String id=rowMap.getString("ID");//id	
								String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//数量
								BigDecimal sl=new BigDecimal(SL);
								String DJ=rowMap.getString("JG");//单价						
								String ZJ=rowMap.getString("ZJ");//总价
								
								String ZCLF=rowMap.getString("ZGSF").isEmpty()?"0":rowMap.getString("ZGSF");//总工时费
								BigDecimal zjNew=new BigDecimal(ZJ);
								
								BigDecimal zclfNew=new BigDecimal(ZCLF);
								if(comtMap.containsKey(BM)) {
									System.out.println("原材料中物料编码："+BM);
									JSONObject json=comtMap.get(BM);							
									String newdj=json.getString("DJ");//
									
									String newclf=json.getString("RGF");
									BigDecimal djNew=new BigDecimal(newdj);
									
									BigDecimal clfNew=new BigDecimal(newclf);
									isTrue="1";
									 //判断是否为动态计算模块
									 if(true) {
										 //物料数量计算公司
										 String equation = rowMap.getString("WLJSGS");
										 //存在计算公式时，只需要更新物料的单价，材料费、人工费、采购费、外协费
										 //存在计算公式时，只需要更新物料的单价，材料费、人工费、采购费、外协费
										 Document document = Jsoup.parse(equation);
										  String newQuation=document.text();
										  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
											  System.out.println("参数计算公式："+newQuation);
												// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
											  if(pMap.size()>0) {
												  for (String key1 : pMap.keySet()) {
														int num = newQuation.indexOf(key1);
														if (num > -1) {
															newQuation = newQuation.replace(key1, noMap.get(key1));
														}
													}
													System.out.println("转换后的参数计算公式："+newQuation);
													// 计算出物料数量和当前模块的物料成本价格
													FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
													Cell cellB1 = sheet.createRow(cs.size()+i).createCell(2);
													cellB1.setCellFormula(newQuation);
													formulaEvaluator.evaluateFormulaCell(cellB1);
													// 获取公式计算结果
													CellValue cellValue = formulaEvaluator.evaluate(cellB1);
													BigDecimal number=new BigDecimal(cellValue.getNumberValue());	
													sl=number; 
											  }
												
										  }
											    //新的物料总价为新的单价x数量									
												zjNew=djNew.multiply(sl);
												
												//新的物料总材料费为新的材料费x数量											
												zclfNew=sl.multiply(clfNew);
												 rowMap.put("JG", newdj);
												 rowMap.put("SL", sl);
												 rowMap.put("WRGF", newclf);
												 rowMap.put("ZGSF", zclfNew);
												 rowMap.put("ZJ", zjNew);
											    // DBSql.update(jssql,new Object[] {newdj,newclf,zjNew,zclfNew,BM,id});

												// String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set DJ= '"+djnum+"',RGF= '"+clfnum+"' ,ZJ='"+zjNew+"',ZGSF='"+zclfNew+"' where WLBM='"+BM+"' and ID='"+id+"' ; ";
													//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
												//	sqlList.add(upSql);

													 //不是动态计算模块时，需要计算每个物料的价格和模块的价格。										
												     //新的装配件总价=原装配件单价-子件总价+新子件单价x子件数量
													djnum=djnum.subtract(zjNew).add(djNew.multiply(sl));
													
													zjNew=djNew.multiply(sl);
													
													//判断材料费是否相等,不相等，则计算当前装配件的材料费，并计算当前装配件子项的物料的材料费。											
													String CLF=rowMap.getString("WRGF");//材料费
												    BigDecimal clfOld=new BigDecimal(CLF);
													if(clfNew.compareTo(clfOld) != 0) {
																	BigDecimal zclfOld=new BigDecimal(ZCLF);
																	zclfNew=sl.multiply(clfNew);
																	clfnum=clfnum.subtract(zclfOld).add(sl.multiply(clfNew));
													}
													//更新当前模块物料子项的数据。												
												     //DBSql.update(jssql,new Object[] {newdj,newclf,zjNew,zclfNew,BM,id});

													 String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set DJ= '"+newdj+"',RGF= '"+newclf+"' ,ZJ='"+zjNew+"',ZGSF='"+zclfNew+"' where WLBM='"+BM+"' and ID='"+id+"' ; ";
														//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
														sqlList.add(upSql);
												 
											     
										  
									 }	
								}
								//将数据更新到lists集合中
								 lists.add(rowMap);
							 }
								//更新装配件数据。
							 if(isTrue.equals("1")) {			                      
			                            Map<String, Object> map=new HashMap<>();	    						    					    					
				    					JSONObject json=new JSONObject();
				    					json.put("SITRUE", SITRUE);
				    					json.put("MKDJ", djnum);
				    					
				    					json.put("RGF", clfnum);
				    					json.put("CLF", data.getString("CLF"));
										
				    					json.put("BM", key);
				    					map.put("SITRUE", SITRUE);	
				    					//map.put("datas", json);
				    					modulLists.put(BH, map);
				    					map.put("datas", lists);
				    					modulLists.put(BH, map);	
				    					String bl=data.getString("ZZFBL").isEmpty()?"0":data.getString("ZZFBL");
				    					String cl=data.getString("CLF");
				    					BigDecimal blnum = new BigDecimal(bl);
				    					BigDecimal rgfnum = new BigDecimal(cl);
				    					BigDecimal num = new BigDecimal("100");
				    					BigDecimal num1 = new BigDecimal("1");
				    					BigDecimal zzf=	clfnum.add(rgfnum).divide(num1.subtract(blnum.divide(num)), 4).multiply(blnum.divide(num));								    									    									    					
				    					 //DBSql.update(modulsql, new Object[] {djnum,clfnum,zzf,zzf.add(djnum),key});	
				    					String upSql= "update BO_EU_CPQ_MODULE_MSG set MKDJ= '"+djnum+"',CLF= '"+clfnum+"' ,ZZF='"+zzf+"',DJZZF='"+zzf.add(djnum)+"' where MKBM='"+key+"' ; ";
											//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											sqlLists.add(upSql);				    								    								    									    				
							 }	
						 
						} catch (Exception e) {
							throw e;
						}finally {
				  			 try {
									workbook.close();
								} catch (IOException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
						
							}
					 }
					 
					 if(sqlList.size()>0) {
						 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
						 DBSql.batch(array3);
					}
					if(sqlLists.size()>0) {
						String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);					
						DBSql.batch(array2);  
					}	
				 }
				 return  modulLists;
		} catch (Exception e) {
			throw e;
		}
	}
	/**
	 * 对外协单价进行更新后，模块中的模块单价和外协费进行更新
	 * @param comtMap
	 * @return
	 */
	public static  Map<String, Map<String, Object>> outsourcingRefreshModule( Map<String, JSONObject>  comtMap ) {
		try {
			//2、获取所有状态为启用的模块信息BO_EU_CPQ_MODULE_MSG
			 String allModelsql="select   a.MKBM,a.MKDJ,"
			 		+ "a.CLF,"
			 		+ "a.RGF,a.WXWG ,a.CGF,a.ZL,a.TJ,a.SITRUE,b.ID,b.WLBM,b.LX ," + 
				 		"b.SL,b.WLJSGS,b.DJ as JG,"
				 		+ "b.CLF as WCLF,"
				 		+ "b.RGF as WRGF,b.WXWG as WX,b.CGF as WCGF,b.ZL as WZL,b.TJ as WTJ,"
				 		+ "b.ZJ,"
				 		+ "b.ZCLF,b.ZGSF,b.ZWX,b.ZCGF,b.ZZL,b.ZTJ " + 
				 		"from  BO_EU_CPQ_MODULE_MSG a,BO_EU_CPQ_MODULE_MSG_GRID b  "
				 		+ "where  a.BINDID=b.BINDID and  a.STATUS='启用'";
			 
			 List<RowMap> modelmaps=DBSql.getMaps(allModelsql);
			 //存放需要更新的模板信息
			 Map<String , List<RowMap>> modelMap=new  HashMap<>();
			 if(modelmaps.size()>0) {
				 for (RowMap rowMap : modelmaps) {
					 String bh=rowMap.getString("MKBM");
						if(modelMap.containsKey(bh)) {
						  List<RowMap> lists= modelMap.get(bh);
						  lists.add(rowMap);
						  modelMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							modelMap.put(bh, lists);
						}
				}
			 }
			 //获取参数信息
			 String csxx="select  b.MKBM,a.PARMENTIER_NO,a.PARMENTIER_VALUE from BO_EU_CPQ_MODULE_MSG_PRICE_PARAMETERS  a , BO_EU_CPQ_MODULE_MSG b where  a.BINDID=b.BINDID and  b.STATUS='启用'";
			 List<RowMap> cs=DBSql.getMaps(csxx); 
			 //存放模块关联的参数信息
			 Map<String , List<RowMap>> parmMap=new  HashMap<>();
			 if(cs.size()>0) {
				 for (RowMap rowMap : cs) {
					 String bh=rowMap.getString("MKBM");
						if(parmMap.containsKey(bh)) {
						  List<RowMap> lists= parmMap.get(bh);
						  lists.add(rowMap);
						  parmMap.put(bh, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							parmMap.put(bh, lists);
						}
				}
			 }
			 String  sql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		+ "WXWG=?  "
				 		+ "where WLBM=? and  ID=?";
			 String  jssql="update BO_EU_CPQ_MODULE_MSG_GRID set DJ=?,"
			 		+ "ZJ=?,"
				 		+ "WXWG=?,ZWX=? where WLBM=? and  ID=?";
			 String modulsql="update BO_EU_CPQ_MODULE_MSG set MKDJ=?,"
			 		+ "WXWG=? "
				 		+ " where MKBM=? ";
			 //用于存放更新的模块编码
				Map<String, Map<String, Object>>modulLists=new HashMap<>();
				List<String> sqlList=new ArrayList<>();
				List<String> sqlLists=new ArrayList<>();
				if(modelMap.size()>0) {
					 for (String key : modelMap.keySet()) {
						 Workbook workbook = new XSSFWorkbook();
						 try {

							 List<RowMap> materials=modelMap.get(key);
							 RowMap data=materials.get(0);
							 String dj=data.getString("MKDJ");//单价						 
							 BigDecimal djnum=new BigDecimal(dj);	
							
							 String BH=data.getString("MKBM");//物料编号
							 String SITRUE=data.getString("SITRUE");//是否动态计算模块
							 String wxf=data.getString("WXWG");//外协费
							 BigDecimal wxfnum=new BigDecimal(wxf);	
							 //存放动态计算的模块物料信息
							 List<RowMap> lists=new  ArrayList<>();
							 String isTrue="0"; 
							//将参数进行遍历放置：
							 List<RowMap> parms=new ArrayList<>();
							 if(parmMap.containsKey(key)) {
								 parms=parmMap.get(key);
							 }
							 Map<String, String> noMap = new HashMap<>();
							 Map<String, String> pMap = new HashMap<String, String>();
							 Sheet sheet = workbook.createSheet("Sheet1");
							 if(parms.size()>0) {
									
									for (int i = 0; i < parms.size(); i++) {
										int num = i + 1;
										Cell cellB1 = sheet.createRow(i).createCell(1);
										//JSONObject jsonObject = (JSONObject) list.get(i);
										RowMap jsonObject =  parms.get(i);
										String bh =  jsonObject.getString("PARMENTIER_NO");
										String value=	jsonObject.getString("PARMENTIER_VALUE");
										if(NumberUtils.isCreatable(value)) {
											double  numValue=Double.parseDouble(value);		
											 cellB1.setCellValue(numValue);
											 System.out.println("cellB1:"+cellB1.getNumericCellValue());
										}else {
											cellB1.setCellValue(value);
											System.out.println("cellB1:"+cellB1.getStringCellValue());											
										}
										noMap.put(bh, "B$" + num);
										pMap.put(bh, value);
									}
							 }
							 for (int i = 0; i < materials.size(); i++) {
								//获取模块子项的物料明细
								 RowMap rowMap = materials.get(i);
								String BM=rowMap.getString("WLBM");//编码		
								String id=rowMap.getString("ID");//id	
								String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//数量
								BigDecimal sl=new BigDecimal(SL);
								String DJ=rowMap.getString("JG");//单价							
								String ZJ=rowMap.getString("ZJ");//总价							
								String ZCLF=rowMap.getString("ZGSF");//总工时费
								String ZWX=rowMap.getString("ZWX");//总外协费
								BigDecimal zjNew=new BigDecimal(ZJ);							
								BigDecimal zclfNew=new BigDecimal(ZCLF);
								BigDecimal zwxfNew=new BigDecimal(ZWX);
								if(comtMap.containsKey(BM)) {
									JSONObject json=comtMap.get(BM);							
									String newdj=json.getString("DJ");// 								
									String newclf=json.getString("RGF");
									String newwxf=json.getString("WXWG");
									BigDecimal djNew=new BigDecimal(newdj);							
									BigDecimal wxfNew=new BigDecimal(newwxf);
									isTrue="1";
									 //判断是否为动态计算模块
									 if(true) {
										 //物料数量计算公司
										 String equation = rowMap.getString("WLJSGS");
										 //存在计算公式时，只需要更新物料的单价，材料费、人工费、采购费、外协费
										 Document document = Jsoup.parse(equation);
										  String newQuation=document.text();
										  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
											  System.out.println("参数计算公式："+newQuation);
												// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
											  if(pMap.size()>0) {
												  for (String key1 : pMap.keySet()) {
														int num = newQuation.indexOf(key1);
														if (num > -1) {
															newQuation = newQuation.replace(key1, noMap.get(key1));
														}
													}
													System.out.println("转换后的参数计算公式："+newQuation);
													// 计算出物料数量和当前模块的物料成本价格
													FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
													Cell cellB1 = sheet.createRow(cs.size()+i).createCell(2);
													cellB1.setCellFormula(newQuation);
													formulaEvaluator.evaluateFormulaCell(cellB1);
													// 获取公式计算结果
													CellValue cellValue = formulaEvaluator.evaluate(cellB1);
													BigDecimal number=new BigDecimal(cellValue.getNumberValue());	
													sl=number;  
											  }
												
										  }
										//不是动态计算模块时，需要计算每个物料的价格和模块的价格。										
										     //新的模块总价=原模块单价-子件总价+新子件单价x子件数量
											djnum=djnum.subtract(zjNew).add(djNew.multiply(sl));											
											    //新的物料总价为新的单价x数量									
												zjNew=djNew.multiply(sl);											
												//新的物料总外协费为新的外协费x数量											
												zwxfNew=sl.multiply(wxfNew);
												 rowMap.put("JG", newdj);											
												 rowMap.put("WX", newwxf);
												 rowMap.put("ZWX", zwxfNew);
												 rowMap.put("ZJ", zjNew);											 
												 rowMap.put("SL", sl);
													// String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set DJ= '"+newdj+"',ZJ= '"+zjNew+"',WXWG='"+newwxf+"' ,ZWX='"+zwxfNew+"' where WLBM='"+BM+"' and  ID='"+id+"' ; ";													 
													//	sqlList.add(upSql);
														 								
														//判断外协费是否相等,不相等，则计算当前子件的外协费，并计算当前装配件子项的物料的外协费。											
														String WX=rowMap.getString("WX");//外协费
													    BigDecimal wxfOld=new BigDecimal(WX);														
														BigDecimal zwxfOld=new BigDecimal(ZWX);
														zwxfNew=sl.multiply(wxfNew);
														wxfnum=wxfnum.subtract(zwxfOld).add(sl.multiply(wxfNew));														
														//更新当前模块物料子项的数据。																										
														 String upSql= "update BO_EU_CPQ_MODULE_MSG_GRID set DJ= '"+newdj+"',ZJ= '"+zjNew+"',WXWG='"+newwxf+"' ,ZWX='"+zwxfNew+"' where WLBM='"+BM+"' and  ID='"+id+"' ; ";
														 //upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
															sqlList.add(upSql);
										  
									 }	
								}
								//将数据更新到lists集合中
								 lists.add(rowMap);
							 }
								//更新装配件数据。
							 if(isTrue.equals("1")) {			                 
			                            Map<String, Object> map=new HashMap<>();	    						    					    					
				    					JSONObject json=new JSONObject();
				    					json.put("SITRUE", SITRUE);
				    					json.put("MKDJ", djnum);			    					
				    					json.put("WXWG", wxfnum);
				    					json.put("CLF", data.getString("CLF"));									
				    					json.put("BM", key);
				    					map.put("SITRUE", SITRUE);	
				    					map.put("datas", lists);
				    					modulLists.put(BH, map);
				    					 //DBSql.update(modulsql, new Object[] {djnum, wxfnum,key});
										 String upSql= "update BO_EU_CPQ_MODULE_MSG set MKDJ= '"+djnum+"',WXWG= '"+wxfnum+"'  where MKBM='"+key+"' ; ";
											//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											sqlLists.add(upSql);
				    				
							 }	
						 
						} catch (Exception e) {
							throw  e;
						}finally {
				  			 try {
									workbook.close();
								} catch (IOException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
						
							}
					 }
				 }
				if(sqlList.size()>0) {
					 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
					 DBSql.batch(array3);
				}
				if(sqlLists.size()>0) {
					String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);					
					DBSql.batch(array2);  
				}			 				 
				return  modulLists;
		} catch (Exception e) {
			throw e;
		}
		
	}
}
